"""
Case Type   : 系统表
Case Name   : 测试系统表GS_DEPENDENCIES_OBJ字段与数据类型是否正确
Create At   :2024/07/08
Owner       :sungang14
Description : 
    1.查看系统表GS_DEPENDENCIES_OBJ的表结构
        \d GS_DEPENDENCIES_OBJ
    2.该表字段与对应字段数据类型是否正确
         roleid         | oid     
         privilege_type | text    
         admin_option   | boolean
Expect      : 
    1.查看系统表GS_DEPENDENCIES_OBJ表结构成功
    2.该表字段与字段数据类型对应正确
History     : 
"""

import sys
import os
import unittest
from yat.test import Node
from yat.test import macro

sys.path.append(sys.path[0] + "/../")
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH


class IndexFileDamaged(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.logger.info('----------------this is setup-----------------------')
        self.userNode = Node('dbuser')
        self.DB_ENV_PATH = macro.DB_ENV_PATH
        self.DB_INSTANCE_PATH = macro.DB_INSTANCE_PATH
        self.com = Common()
        self.comsh = CommonSH('dbuser')
        self.expect_result_dict = {
            'Column': ['roleid', 'privilege_type', 'admin_option'],
            'Type': ['oid', 'text', 'boolean']
        }
        self.gs_dep_obj_flag = {
            'Column': ['schemaname', 'packagename', 'type', 'name', 'objnode'],
            'Type': ['name', 'name', 'integer', 'text', 'pg_node_tree']
        }

    def test_Index_file_damaged(self):
        self.logger.info('----------------------------查看表结构-----------------------------')
        msg = self.comsh.execut_db_sql("\d GS_DEPENDENCIES_OBJ")
        self.logger.info(msg)
        result_dict = self.com.format_sql_result(msg)
        del result_dict['Modifiers']
        self.assertDictEqual(self.gs_dep_obj_flag, result_dict)
        self.logger.info(
            "不设置behavior_compat_options='plpgsql_dependency'查看系统表GS_DEPENDENCIES_OBJ不记录procedure信息")
        sql_cmd = f'drop table if exists t_systemtable_0110;' \
                  f'drop procedure if exists p_systemtable_0110;' \
                  f"set behavior_compat_options='';" \
                  f'create table t_systemtable_0110(sno int,name varchar,sex varchar,cno int);' \
                  f'create or replace procedure p_systemtable_0110(c t_systemtable_0110.name%Type) as declarev_1 t_systemtable_0110%RowType;' \
                  f'begin NULL;' \
                  f'end;'
        msg = self.comsh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.assertNotIn('public', msg)
        msg = self.comsh.execut_db_sql(
            "select * from GS_DEPENDENCIES_OBJ where name='p_systemtable_0110(pg_catalog.varchar)';")
        self.logger.info(msg)
        self.logger.info(
            "设置behavior_compat_options='plpgsql_dependency'查看系统表GS_DEPENDENCIES_OBJ记录procedure信息")
        sql_cmd = f'drop table if exists t_systemtable_0110;' \
                  f'drop procedure if exists p_systemtable_0110;' \
                  f"set behavior_compat_options='plpgsql_dependency';" \
                  f'create table t_systemtable_0110(sno int,name varchar,sex varchar,cno int);' \
                  f'create or replace procedure p_systemtable_0110(c t_systemtable_0110.name%Type) as declare v_1 t_systemtable_0110%RowType;begin NULL;end;'
        msg = self.comsh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        msg = self.comsh.execut_db_sql(
            "select * from GS_DEPENDENCIES_OBJ where name='p_systemtable_0110(pg_catalog.varchar)';")
        self.logger.info(msg)
        self.assertIn('p_systemtable_0110', msg)

    def tearDown(self):
        self.logger.info('----------------this is tearDown-----------------------')
        sql_cmd = f'drop procedure if exists p_systemtable_0110;' \
                  f'drop table if exists t_systemtable_0110;' \
                  f"set behavior_compat_options='';"
        msg = self.comsh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
